// 数据库封装（使用 better-sqlite3）
import Database from 'better-sqlite3'
import { fileURLToPath } from 'url'
import { dirname, join } from 'path'
import { existsSync } from 'fs'

const __filename = fileURLToPath(import.meta.url)
const __dirname = dirname(__filename)

// 数据库文件路径
const DB_PATH = join(__dirname, '../data.db')

// 创建数据库连接
let db = null

export function getDB() {
  if (!db) {
    db = new Database(DB_PATH)
    // 启用外键约束
    db.pragma('foreign_keys = ON')
  }
  return db
}

// 关闭数据库连接
export function closeDB() {
  if (db) {
    db.close()
    db = null
  }
}

// 初始化数据库表结构
export function initDB() {
  const database = getDB()
  
  // 创建基础表结构（初始版本）
  database.exec(`
    CREATE TABLE IF NOT EXISTS Category (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      slug TEXT UNIQUE NOT NULL,
      description TEXT,
      sort INTEGER DEFAULT 0,
      status INTEGER DEFAULT 1,
      createdAt TEXT DEFAULT (datetime('now')),
      updatedAt TEXT DEFAULT (datetime('now'))
    )
  `)

  database.exec(`
    CREATE TABLE IF NOT EXISTS Image (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT NOT NULL,
      url TEXT NOT NULL,
      thumbnail TEXT,
      width INTEGER,
      height INTEGER,
      size INTEGER,
      categoryId INTEGER NOT NULL,
      status INTEGER DEFAULT 1,
      views INTEGER DEFAULT 0,
      likes INTEGER DEFAULT 0,
      createdAt TEXT DEFAULT (datetime('now')),
      updatedAt TEXT DEFAULT (datetime('now')),
      FOREIGN KEY (categoryId) REFERENCES Category(id)
    )
  `)

  database.exec(`
    CREATE INDEX IF NOT EXISTS idx_category_status ON Category(status);
    CREATE INDEX IF NOT EXISTS idx_category_sort ON Category(sort);
    CREATE INDEX IF NOT EXISTS idx_image_categoryId ON Image(categoryId);
    CREATE INDEX IF NOT EXISTS idx_image_status ON Image(status);
    CREATE INDEX IF NOT EXISTS idx_image_createdAt ON Image(createdAt);
  `)

  database.exec(`
    CREATE TRIGGER IF NOT EXISTS update_category_updatedAt 
    AFTER UPDATE ON Category
    BEGIN
      UPDATE Category SET updatedAt = datetime('now') WHERE id = NEW.id;
    END;

    CREATE TRIGGER IF NOT EXISTS update_image_updatedAt 
    AFTER UPDATE ON Image
    BEGIN
      UPDATE Image SET updatedAt = datetime('now') WHERE id = NEW.id;
    END;
  `)

  // 执行迁移系统（同步导入）
  try {
    // 使用动态导入，但不使用 await（因为这是同步函数）
    import('./migrations/migration-system.js').then(module => {
      module.runMigrations()
    }).catch(err => {
      console.warn('迁移系统加载失败，跳过迁移:', err.message)
      // 执行回退方案
      executeFallbackMigration(database)
    })
  } catch (error) {
    // 如果迁移系统不存在或出错，仍然执行基础初始化
    console.warn('迁移系统加载失败，跳过迁移:', error.message)
    executeFallbackMigration(database)
  }
  
  console.log('✓ 数据库初始化完成')
}

// 回退迁移方案（向后兼容）
function executeFallbackMigration(database) {
    
    // 直接执行原有的广告配置表创建（向后兼容）
    database.exec(`
      CREATE TABLE IF NOT EXISTS AdConfig (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        platform TEXT NOT NULL,
        adType TEXT NOT NULL,
        adUnitId TEXT NOT NULL,
        enabled INTEGER DEFAULT 1,
        strategy TEXT,
        createdAt TEXT DEFAULT (datetime('now')),
        updatedAt TEXT DEFAULT (datetime('now')),
        UNIQUE(platform, adType)
      );

      CREATE TABLE IF NOT EXISTS AppConfig (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        configKey TEXT UNIQUE NOT NULL,
        configValue TEXT,
        description TEXT,
        updatedAt TEXT DEFAULT (datetime('now'))
      );

      CREATE TABLE IF NOT EXISTS AdStats (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        platform TEXT NOT NULL,
        adType TEXT NOT NULL,
        adUnitId TEXT NOT NULL,
        eventType TEXT NOT NULL,
        count INTEGER DEFAULT 1,
        date TEXT DEFAULT (date('now')),
        createdAt TEXT DEFAULT (datetime('now')),
        UNIQUE(platform, adType, adUnitId, eventType, date)
      );

      CREATE INDEX IF NOT EXISTS idx_adconfig_platform ON AdConfig(platform);
      CREATE INDEX IF NOT EXISTS idx_adconfig_enabled ON AdConfig(enabled);
      CREATE INDEX IF NOT EXISTS idx_adstats_date ON AdStats(date);
      CREATE INDEX IF NOT EXISTS idx_adstats_platform_type ON AdStats(platform, adType);
    `)

    database.exec(`
      INSERT OR IGNORE INTO AppConfig (configKey, configValue, description) VALUES
      ('app.version', '1.0.0', '应用版本号'),
      ('app.versionCode', '100', '应用版本代码'),
      ('app.updateRequired', 'false', '是否需要强制更新'),
      ('app.updateMessage', '', '更新提示信息'),
      ('app.updateUrl', '', '更新下载地址'),
      ('app.features.adEnabled', 'true', '广告功能开关'),
      ('app.features.searchEnabled', 'true', '搜索功能开关'),
      ('app.features.downloadEnabled', 'true', '下载功能开关'),
      ('app.recommendations.maxFavorites', '1000', '最大收藏数'),
      ('app.recommendations.maxHistory', '100', '最大历史记录数');

      INSERT OR IGNORE INTO AdConfig (platform, adType, adUnitId, enabled, strategy) VALUES
      ('mp-weixin', 'banner', 'adunit-test-banner', 1, '{"position":"bottom"}'),
      ('mp-weixin', 'interstitial', 'adunit-test-interstitial', 1, '{"browseCount":5,"timeInterval":120000}'),
      ('mp-weixin', 'rewardVideo', 'adunit-test-reward', 1, '{"downloadRequireAd":true}'),
      ('mp-weixin', 'native', 'adunit-test-native', 1, '{}'),
      ('h5', 'banner', 'h5-banner-placeholder', 1, '{"position":"bottom"}'),
      ('h5', 'interstitial', 'h5-interstitial-placeholder', 1, '{"browseCount":5,"timeInterval":120000}'),
      ('h5', 'rewardVideo', 'h5-reward-placeholder', 1, '{"downloadRequireAd":true}'),
      ('h5', 'native', 'h5-native-placeholder', 0, '{}');
    `)
}

